from ConnectMySql import ConnectMySQl
from DumpExcel import DumpExcel
from OutputExcel import OutputExcel
from ReadSheetName import ReadSheetName
def main():
    path = '../EXCEL/tmp.xls'
    read_object = ReadSheetName(path)
    sheet_name_list = read_object.out_sheet_name()

    for i in sheet_name_list:
        calculate(i, i+"成绩分析.xlsx", path)



def calculate(name, filename, path):
    hostip = 'localhost'
    user = 'lmx'
    password = '123456'
    dbname = 'calculate'
    # tmpcap = []
    db_object = ConnectMySQl(hostip, user, password, dbname, name)
    out_excel_object = OutputExcel()

    db_object.connect(name)
    excel_object = DumpExcel(path)
    tmpcap = excel_object.dump_excel(name)

    # 将数据送进MySQL
    db_object.insert_mysql(tmpcap)

    # 处理出班级和专业
    tmplist = db_object.class_major_sql()

    # 获取语数外的均值
    tmplistdict = db_object.calculate_ysw_average(tmplist)

    # 获取专业课的均值
    majortmplistdict = db_object.calculate_major_average(tmplist)

    # 获取排名前50的数据
    ranktmplistdict50 = db_object.calculate_yxw_50_100(tmplist, 50)

    # 获取排名前100的数据
    ranktmplistdict100 = db_object.calculate_yxw_50_100(tmplist, 100)

    # 获取匹配率
    matchdict = db_object.calculate_match(tmplist)

    # 获取年级语数外的均值
    averagedict = db_object.get_class_average()

    # 输出excel
    out_excel_object.out_data_to_excel(filename, tmplistdict, majortmplistdict, ranktmplistdict50,
                                       ranktmplistdict100, matchdict, averagedict)

    # 关闭游标和连接
    db_object.close_sql()



if __name__ == '__main__':
    main()